#!/usr/bin/env ruby
#  jumbalaya_database.rb
#  Created by Paul Gower on 2007-10-24.
#  Copyright (c) 2007. All rights reserved.

require 'rubygems'
require 'sqlite3'

module Jumbalaya
  
  class Database
    attr_reader :connection, :filename, :host, :username, :db, :type # NOTE: password does not get a public accessor method, only the database class itself can get access
    
    def initialize(type, params={})
      @type = type.to_s
      case type
      when :sqlite3
        if not params[:filename] then return end # FIXME: Will die without a decent explanation, make it throw an error
        @filename = params[:filename]
        @connection = SQLite3::Database.new( @filename )
        @connection.results_as_hash = true
        @connection.type_translation = true
        
        def exec_sql(sql)
          r = @connection.execute2(sql)
          c = r.shift
          return {:records => r, :columns => c}
        end
        
      when :mysql # fixme: this is super rough, will most likely not work and will need alot of testing
        if not params[:host] or not params[:username] or not params[:password] or not params[:db] then return end # FIXME: Will die without a decent explanation, make it throw an error
        @host = params[:host]
        @username = params[:username]
        @password = params[:password]
        @db = params[:db]
        @connection = Mysql::new(@host, @username, @password, @db)
        
        def exec_sql(sql)
          results = @connection.query(sql)
        end
      else
        raise Exceptions::UnkownDatabaseType, "Connection type #{type}, does not exist"
      end
    end
    
    # TODO: None of the strings are being checked before being executed, need to properly escape strings
    def create_record(params={})
    def sqlite3_create_record(table_name, params={})
      u_id = sql_exec("SELECT id FROM #{table_name} ORDER BY id DESC").flatten.first.to_i + 1
      self.sqlite3_execute("INSERT INTO #{table_name} VALUES (id=\"#{u_id}\")")
      columns = sqlite3_get_columns(table)
      params = params.delete(:id)
      params.each do |k,v|
        if columns.include?(k.to_s) then
          self.sqlite3_set_value(u_id,k.to_s,v,table)
        end
      end
      u_id
    end

    def get_columns(table_name)
      begin
        r = exec_sql("select * from #{table_name}")
        r.first - ["id"]
      rescue SQLite3::SQLException
        puts "SQLite3, Query failed: \n" + $!
      end
    end

    def get_value(id,column,table_name)
      r = exec_sql("select #{column} from #{table_name} where id=\"#{id}\"")
      r[1].first
    end

    def set_value(id,column,value,table_name)
      sql_exec("update #{table_name} set #{column}=\"#{value}\" where id=\"#{id}\"")
    end

    def find(constraint,table_name)
      if constraint.kind_of?(Integer) then
        sql_exec("select id from #{table_name} where id=\"#{constraint}\"")
      elsif constraint.kind_of?(Symbol) then
        case constraint
        when :first
          sql_exec("SELECT id FROM #{table_name} ORDER BY id ASC").flatten.first.to_i
        when :last
          sql_exec("SELECT id FROM #{table_name} ORDER BY id DESC").flatten.first.to_i
        end
      else
        return nil
      end
    end

    def has_id_by?(constraint, value, table_name)
      sql_exec("select id from #{table_name} where #{constraint}=\"#{value}\"")
    end

    def has_id_by_and?(constraint_one, value_one, constraint_two, value_two, table_name)
      sql_exec("select id from #{table_name} where #{constraint_one}=\"#{value_one}\" and #{constraint_two}=\"#{value_two}\"")
    end
    
    def has_id?(id,table_name)
      r = exec_sql("SELECT id FROM #{table_name} WHERE id=#{id}")
      if r.length < 2 then
        nil
      else
        true
      end
    end
    
  end

  class Exceptions
    # Exceptions
    class UnknownDatabaseType < Exception
    end
    
    class BrokenDatabaseConnection < Exception
    end
  end
  
end